{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from WORLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introduction\n",
    "\n",
    "[Read the notes about this table](https://sqlzoo.net/wiki/Read_the_notes_about_this_table.). Observe the result of running this SQL command to show the name, continent and population of all countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>continent</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Afghanistan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>32225560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Albania</td>\n",
       "        <td>Europe</td>\n",
       "        <td>2845955.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Africa</td>\n",
       "        <td>43000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra</td>\n",
       "        <td>Europe</td>\n",
       "        <td>77543.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Angola</td>\n",
       "        <td>Africa</td>\n",
       "        <td>31127674.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antigua and Barbuda</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>96453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>South America</td>\n",
       "        <td>44938712.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Eurasia</td>\n",
       "        <td>2957500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>Oceania</td>\n",
       "        <td>25690023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>Europe</td>\n",
       "        <td>8902600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Azerbaijan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>10067108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahamas</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>385340.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahrain</td>\n",
       "        <td>Asia</td>\n",
       "        <td>1543300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>Asia</td>\n",
       "        <td>168506944.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbados</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>287025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belarus</td>\n",
       "        <td>Europe</td>\n",
       "        <td>9408400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>Europe</td>\n",
       "        <td>11524454.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belize</td>\n",
       "        <td>North America</td>\n",
       "        <td>408487.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Benin</td>\n",
       "        <td>Africa</td>\n",
       "        <td>11733059.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bhutan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>741672.0</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">195 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Afghanistan', 'Asia', 32225560.0),\n",
       " ('Albania', 'Europe', 2845955.0),\n",
       " ('Algeria', 'Africa', 43000000.0),\n",
       " ('Andorra', 'Europe', 77543.0),\n",
       " ('Angola', 'Africa', 31127674.0),\n",
       " ('Antigua and Barbuda', 'Caribbean', 96453.0),\n",
       " ('Argentina', 'South America', 44938712.0),\n",
       " ('Armenia', 'Eurasia', 2957500.0),\n",
       " ('Australia', 'Oceania', 25690023.0),\n",
       " ('Austria', 'Europe', 8902600.0),\n",
       " ('Azerbaijan', 'Asia', 10067108.0),\n",
       " ('Bahamas', 'Caribbean', 385340.0),\n",
       " ('Bahrain', 'Asia', 1543300.0),\n",
       " ('Bangladesh', 'Asia', 168506944.0),\n",
       " ('Barbados', 'Caribbean', 287025.0),\n",
       " ('Belarus', 'Europe', 9408400.0),\n",
       " ('Belgium', 'Europe', 11524454.0),\n",
       " ('Belize', 'North America', 408487.0),\n",
       " ('Benin', 'Africa', 11733059.0),\n",
       " ('Bhutan', 'Asia', 741672.0),\n",
       " ('Bolivia', 'South America', 11469896.0),\n",
       " ('Bosnia and Herzegovina', 'Europe', 3301000.0),\n",
       " ('Botswana', 'Africa', 2338851.0),\n",
       " ('Brazil', 'South America', 211442625.0),\n",
       " ('Brunei', 'Asia', 442400.0),\n",
       " ('Bulgaria', 'Europe', 6951482.0),\n",
       " ('Burkina Faso', 'Africa', 20870060.0),\n",
       " ('Burundi', 'Africa', 10953317.0),\n",
       " ('Cabo Verde', 'Africa', 491875.0),\n",
       " ('Cambodia', 'Asia', 15288489.0),\n",
       " ('Cameroon', 'Africa', 26545864.0),\n",
       " ('Canada', 'North America', 38007166.0),\n",
       " ('Central African Republic', 'Africa', 5496011.0),\n",
       " ('Chad', 'Africa', 16244513.0),\n",
       " ('Chile', 'South America', 19107216.0),\n",
       " ('China', 'Asia', 1402378640.0),\n",
       " ('Colombia', 'South America', 49395678.0),\n",
       " ('Comoros', 'Africa', 873724.0),\n",
       " ('Congo, Democratic Republic of', 'Africa', 69360000.0),\n",
       " ('Congo, Republic of', 'Africa', 4559000.0),\n",
       " ('Costa Rica', 'North America', 5058007.0),\n",
       " (\"Côte d'Ivoire\", 'Africa', 23919000.0),\n",
       " ('Croatia', 'Europe', 4076246.0),\n",
       " ('Cuba', 'Caribbean', 11209628.0),\n",
       " ('Cyprus', 'Asia', 875900.0),\n",
       " ('Czech Republic', 'Europe', 10693939.0),\n",
       " ('Denmark', 'Europe', 5822763.0),\n",
       " ('Djibouti', 'Africa', 1078373.0),\n",
       " ('Dominica', 'Caribbean', 71808.0),\n",
       " ('Dominican Republic', 'Caribbean', 10358320.0),\n",
       " ('Ecuador', 'South America', 17472948.0),\n",
       " ('Egypt', 'Asia', 100300578.0),\n",
       " ('El Salvador', 'North America', 6486201.0),\n",
       " ('Equatorial Guinea', 'Africa', 1358276.0),\n",
       " ('Eritrea', 'Africa', 3497117.0),\n",
       " ('Estonia', 'Europe', 1328360.0),\n",
       " ('Eswani', 'Africa', 1268000.0),\n",
       " ('Ethiopia', 'Africa', 98665000.0),\n",
       " ('Fiji', 'Oceania', 884887.0),\n",
       " ('Finland', 'Europe', 5527573.0),\n",
       " ('France', 'Europe', 67076000.0),\n",
       " ('Gabon', 'Africa', 2172579.0),\n",
       " ('Gambia', 'Africa', 2347706.0),\n",
       " ('Georgia', 'Asia', 3723464.0),\n",
       " ('Germany', 'Europe', 83149300.0),\n",
       " ('Ghana', 'Africa', 30280811.0),\n",
       " ('Greece', 'Europe', 10724599.0),\n",
       " ('Grenada', 'Caribbean', 112003.0),\n",
       " ('Guatemala', 'North America', 16604026.0),\n",
       " ('Guinea', 'Africa', 12218357.0),\n",
       " ('Guinea-Bissau', 'Africa', 1604528.0),\n",
       " ('Guyana', 'South America', 782766.0),\n",
       " ('Haiti', 'Caribbean', 11577779.0),\n",
       " ('Honduras', 'North America', 9158345.0),\n",
       " ('Hungary', 'Europe', 9772756.0),\n",
       " ('Iceland', 'Europe', 364260.0),\n",
       " ('India', 'Asia', 1361503224.0),\n",
       " ('Indonesia', 'Asia', 266911900.0),\n",
       " ('Iran', 'Asia', 83396540.0),\n",
       " ('Iraq', 'Asia', 39127900.0),\n",
       " ('Ireland', 'Europe', 4921500.0),\n",
       " ('Israel', 'Asia', 9190800.0),\n",
       " ('Italy', 'Europe', 60238522.0),\n",
       " ('Jamaica', 'Caribbean', 2726667.0),\n",
       " ('Japan', 'Asia', 125950000.0),\n",
       " ('Jordan', 'Asia', 10666772.0),\n",
       " ('Kazakhstan', 'Europe', 18689256.0),\n",
       " ('Kenya', 'Africa', 47564296.0),\n",
       " ('Kiribati', 'Oceania', 120100.0),\n",
       " ('Kuwait', 'Asia', 4420110.0),\n",
       " ('Kyrgyzstan', 'Asia', 6533500.0),\n",
       " ('Laos', 'Asia', 7123205.0),\n",
       " ('Latvia', 'Europe', 1906800.0),\n",
       " ('Lebanon', 'Asia', 6825442.0),\n",
       " ('Lesotho', 'Africa', 2007201.0),\n",
       " ('Liberia', 'Africa', 4475353.0),\n",
       " ('Libya', 'Africa', 6871287.0),\n",
       " ('Liechtenstein', 'Europe', 38749.0),\n",
       " ('Lithuania', 'Europe', 2793471.0),\n",
       " ('Luxembourg', 'Europe', 626108.0),\n",
       " ('Madagascar', 'Africa', 26251309.0),\n",
       " ('Malawi', 'Africa', 19129952.0),\n",
       " ('Malaysia', 'Asia', 32761760.0),\n",
       " ('Maldives', 'Asia', 374775.0),\n",
       " ('Mali', 'Africa', 20250833.0),\n",
       " ('Malta', 'Europe', 493559.0),\n",
       " ('Marshall Islands', 'Oceania', 55500.0),\n",
       " ('Mauritania', 'Africa', 4077347.0),\n",
       " ('Mauritius', 'Africa', 1265985.0),\n",
       " ('Mexico', 'North America', 126577691.0),\n",
       " ('Micronesia', ' Federated States of,Oceania', 101351.0),\n",
       " ('Moldova', 'Europe', 2681735.0),\n",
       " ('Monaco', 'Europe', 38300.0),\n",
       " ('Mongolia', 'Asia', 3314524.0),\n",
       " ('Montenegro', 'Europe', 622359.0),\n",
       " ('Morocco', 'Africa', 35879846.0),\n",
       " ('Mozambique', 'Africa', 30066648.0),\n",
       " ('Myanmar', 'Asia', 54339766.0),\n",
       " ('Namibia', 'Africa', 2458936.0),\n",
       " ('Nauru', 'Oceania', 11000.0),\n",
       " ('Nepal', 'Asia', 29996478.0),\n",
       " ('Netherlands', 'Europe', 17459431.0),\n",
       " ('New Zealand', 'Oceania', 4981058.0),\n",
       " ('Nicaragua', 'North America', 6460411.0),\n",
       " ('Niger', 'Africa', 22314743.0),\n",
       " ('Nigeria', 'Africa', 206139587.0),\n",
       " ('North Korea', 'Asia', 25450000.0),\n",
       " ('North Macedonia', 'Europe', 2077132.0),\n",
       " ('Norway', 'Europe', 5367580.0),\n",
       " ('Oman', 'Asia', 4664790.0),\n",
       " ('Pakistan', 'Asia', 219412520.0),\n",
       " ('Palau', 'Oceania', 17900.0),\n",
       " ('Panama', 'North America', 4218808.0),\n",
       " ('Papua New Guinea', 'Oceania', 8935000.0),\n",
       " ('Paraguay', 'South America', 7252672.0),\n",
       " ('Peru', 'South America', 32131400.0),\n",
       " ('Philippines', 'Asia', 108545308.0),\n",
       " ('Poland', 'Europe', 38379000.0),\n",
       " ('Portugal', 'Europe', 10276617.0),\n",
       " ('Qatar', 'Asia', 2747282.0),\n",
       " ('Romania', 'Europe', 19405156.0),\n",
       " ('Russia', 'Eurasia', 146745098.0),\n",
       " ('Rwanda', 'Africa', 12374397.0),\n",
       " ('Saint Kitts and Nevis', 'North America', 52823.0),\n",
       " ('Saint Lucia', 'Caribbean', 178696.0),\n",
       " ('Saint Vincent and the Grenadines', 'South America', 110608.0),\n",
       " ('Samoa', 'Oceania', 200874.0),\n",
       " ('San Marino', 'Europe', 33574.0),\n",
       " ('Sao Tomé and Príncipe', 'Africa', 201784.0),\n",
       " ('Saudi Arabia', 'Asia', 34218169.0),\n",
       " ('Senegal', 'Africa', 16209125.0),\n",
       " ('Serbia', 'Europe', 6963764.0),\n",
       " ('Seychelles', 'Africa', 97625.0),\n",
       " ('Sierra Leone', 'Africa', 7901454.0),\n",
       " ('Singapore', 'Asia', 5703600.0),\n",
       " ('Slovakia', 'Europe', 5456362.0),\n",
       " ('Slovenia', 'Europe', 2094060.0),\n",
       " ('Solomon Islands', 'Oceania', 680806.0),\n",
       " ('Somalia', 'Africa', 15893219.0),\n",
       " ('South Africa', 'Africa', 58775022.0),\n",
       " ('South Korea', 'Asia', 51780579.0),\n",
       " ('South Sudan', 'Africa', 12778250.0),\n",
       " ('Spain', 'Europe', 47100396.0),\n",
       " ('Sri Lanka', 'Asia', 21803000.0),\n",
       " ('Sudan', 'Africa', 42456380.0),\n",
       " ('Suriname', 'South America', 581372.0),\n",
       " ('Sweden', 'Europe', 10338368.0),\n",
       " ('Switzerland', 'Europe', 8603899.0),\n",
       " ('Syria', 'Asia', 17500657.0),\n",
       " ('Taiwan', 'Asia', 23604265.0),\n",
       " ('Tajikistan', 'Asia', 9127000.0),\n",
       " ('Tanzania', 'Africa', 55890747.0),\n",
       " ('Thailand', 'Asia', 66497905.0),\n",
       " ('Timor-Leste', 'Asia', 1212107.0),\n",
       " ('Togo', 'Africa', 7538000.0),\n",
       " ('Tonga', 'Oceania', 100651.0),\n",
       " ('Trinidad and Tobago', 'Caribbean', 1363985.0),\n",
       " ('Tunisia', 'Africa', 11722038.0),\n",
       " ('Turkey', 'Asia', 83154997.0),\n",
       " ('Turkmenistan', 'Asia', 6031187.0),\n",
       " ('Tuvalu', 'Oceania', 10200.0),\n",
       " ('Uganda', 'Africa', 40299300.0),\n",
       " ('Ukraine', 'Europe', 41858119.0),\n",
       " ('United Arab Emirates', 'Asia', 9890400.0),\n",
       " ('United Kingdom', 'Europe', 66435550.0),\n",
       " ('United States', 'North America', 329583916.0),\n",
       " ('Uruguay', 'South America', 3518552.0),\n",
       " ('Uzbekistan', 'Asia', 34148357.0),\n",
       " ('Vanuatu', 'Oceania', 304500.0),\n",
       " ('Vatican City', 'Europe', 825.0),\n",
       " ('Venezuela', 'South America', 32219521.0),\n",
       " ('Vietnam', 'Asia', 96208984.0),\n",
       " ('Yemen', 'Asia', 29825968.0),\n",
       " ('Zambia', 'Africa', 17885422.0),\n",
       " ('Zimbabwe', 'Africa', 15159624.0)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, continent, population FROM world"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Large Countries\n",
    "\n",
    "[How to use WHERE to filter records](https://sqlzoo.net/wiki/WHERE_filters). Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Brazil',),\n",
       " ('China',),\n",
       " ('India',),\n",
       " ('Indonesia',),\n",
       " ('Nigeria',),\n",
       " ('Pakistan',),\n",
       " ('United States',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE population>=200000000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Per capita GDP\n",
    "\n",
    "Give the `name` and the **per capita GDP** for those countries with a `population` of at least 200 million.\n",
    "\n",
    "> _HELP:How to calculate per capita GDP_   \n",
    "> per capita GDP is the GDP divided by the population GDP/population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pcg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>9721.370040690708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>8724.306439807155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1891.7810509716428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>3804.7722862862242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "        <td>1822.8861591732984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "        <td>1377.036278513186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>59121.192066908996</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Brazil', 9721.370040690708),\n",
       " ('China', 8724.306439807155),\n",
       " ('India', 1891.7810509716428),\n",
       " ('Indonesia', 3804.7722862862242),\n",
       " ('Nigeria', 1822.8861591732984),\n",
       " ('Pakistan', 1377.036278513186),\n",
       " ('United States', 59121.192066908996)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, gdp/population as pcg FROM world WHERE population > 200000000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. South America In millions\n",
    "\n",
    "Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>44.938712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bolivia</td>\n",
       "        <td>11.469896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>211.442625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chile</td>\n",
       "        <td>19.107216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colombia</td>\n",
       "        <td>49.395678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>17.472948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>0.782766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>7.252672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>32.1314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Vincent and the Grenadines</td>\n",
       "        <td>0.110608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suriname</td>\n",
       "        <td>0.581372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uruguay</td>\n",
       "        <td>3.518552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>32.219521</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Argentina', 44.938712),\n",
       " ('Bolivia', 11.469896),\n",
       " ('Brazil', 211.442625),\n",
       " ('Chile', 19.107216),\n",
       " ('Colombia', 49.395678),\n",
       " ('Ecuador', 17.472948),\n",
       " ('Guyana', 0.782766),\n",
       " ('Paraguay', 7.252672),\n",
       " ('Peru', 32.1314),\n",
       " ('Saint Vincent and the Grenadines', 0.110608),\n",
       " ('Suriname', 0.581372),\n",
       " ('Uruguay', 3.518552),\n",
       " ('Venezuela', 32.219521)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population/1000000 AS population FROM world WHERE continent='South America'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. France, Germany, Italy\n",
    "\n",
    "Show the `name` and `population` for France, Germany, Italy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>67076000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>83149300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>60238522.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('France', 67076000.0), ('Germany', 83149300.0), ('Italy', 60238522.0)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. United\n",
    "\n",
    "Show the countries which have a `name` that includes the word 'United'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United Arab Emirates',), ('United Kingdom',), ('United States',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%United%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Two ways to be big\n",
    "\n",
    "Two ways to be big: A country is **big** if it has an area of more than 3 million sq km or it has a population of more than 250 million.\n",
    "\n",
    "**Show the countries that are big by area or big by population. Show name, population and area.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>25690023.0</td>\n",
       "        <td>7692024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>211442625.0</td>\n",
       "        <td>8515767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>38007166.0</td>\n",
       "        <td>9984670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>1402378640.0</td>\n",
       "        <td>9596961.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1361503224.0</td>\n",
       "        <td>3166414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>266911900.0</td>\n",
       "        <td>1904569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>146745098.0</td>\n",
       "        <td>17125242.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>329583916.0</td>\n",
       "        <td>9826675.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', 25690023.0, 7692024.0),\n",
       " ('Brazil', 211442625.0, 8515767.0),\n",
       " ('Canada', 38007166.0, 9984670.0),\n",
       " ('China', 1402378640.0, 9596961.0),\n",
       " ('India', 1361503224.0, 3166414.0),\n",
       " ('Indonesia', 266911900.0, 1904569.0),\n",
       " ('Russia', 146745098.0, 17125242.0),\n",
       " ('United States', 329583916.0, 9826675.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population, area FROM world\n",
    "WHERE population >= 250000000 OR area >= 3000000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. One or the other (but not both)\n",
    "\n",
    "**Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.**\n",
    "\n",
    "- Australia has a big area but a small population, it should be **included**.\n",
    "- Indonesia has a big population but a small area, it should be **included**.\n",
    "- China has a big population **and** big area, it should be **excluded**.\n",
    "- United Kingdom has a small population and a small area, it should be **excluded**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>25690023.0</td>\n",
       "        <td>7692024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>211442625.0</td>\n",
       "        <td>8515767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>38007166.0</td>\n",
       "        <td>9984670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>266911900.0</td>\n",
       "        <td>1904569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>146745098.0</td>\n",
       "        <td>17125242.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', 25690023.0, 7692024.0),\n",
       " ('Brazil', 211442625.0, 8515767.0),\n",
       " ('Canada', 38007166.0, 9984670.0),\n",
       " ('Indonesia', 266911900.0, 1904569.0),\n",
       " ('Russia', 146745098.0, 17125242.0)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population, area FROM world\n",
    "WHERE (population >= 250000000 OR area >= 3000000) AND NOT\n",
    "(population >= 250000000 AND area >= 3000000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Rounding\n",
    "\n",
    "Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the [ROUND](https://sqlzoo.net/wiki/ROUND) function to show the values to two decimal places.\n",
    "\n",
    "**For South America show population in millions and GDP in billions both to 2 decimal places.**\n",
    "\n",
    "> _Millions and billions_    \n",
    "> Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>popl</th>\n",
       "        <th>gdp_mil</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>44.94</td>\n",
       "        <td>637.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bolivia</td>\n",
       "        <td>11.47</td>\n",
       "        <td>37.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>211.44</td>\n",
       "        <td>2055.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chile</td>\n",
       "        <td>19.11</td>\n",
       "        <td>277.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colombia</td>\n",
       "        <td>49.4</td>\n",
       "        <td>309.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>17.47</td>\n",
       "        <td>104.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>0.78</td>\n",
       "        <td>3.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>7.25</td>\n",
       "        <td>29.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>32.13</td>\n",
       "        <td>211.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Vincent and the Grenadines</td>\n",
       "        <td>0.11</td>\n",
       "        <td>0.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suriname</td>\n",
       "        <td>0.58</td>\n",
       "        <td>5.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uruguay</td>\n",
       "        <td>3.52</td>\n",
       "        <td>59.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>32.22</td>\n",
       "        <td>255.09</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Argentina', 44.94, 637.49),\n",
       " ('Bolivia', 11.47, 37.51),\n",
       " ('Brazil', 211.44, 2055.51),\n",
       " ('Chile', 19.11, 277.08),\n",
       " ('Colombia', 49.4, 309.19),\n",
       " ('Ecuador', 17.47, 104.3),\n",
       " ('Guyana', 0.78, 3.09),\n",
       " ('Paraguay', 7.25, 29.44),\n",
       " ('Peru', 32.13, 211.4),\n",
       " ('Saint Vincent and the Grenadines', 0.11, 0.73),\n",
       " ('Suriname', 0.58, 5.21),\n",
       " ('Uruguay', 3.52, 59.18),\n",
       " ('Venezuela', 32.22, 255.09)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, ROUND(population/1000000, 2) as popl, ROUND(gdp/1000000000, 2) as gdp_mil\n",
    "FROM world\n",
    "WHERE continent = 'South America'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Trillion dollar economies\n",
    "\n",
    "Show the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.\n",
    "\n",
    "**Show per-capita GDP for the trillion dollar countries to the nearest $1000.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pc_gdp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>55000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>43000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>9000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>39000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>44000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>4000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>32000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>39000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>9000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>22000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>28000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>59000.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', 55000.0),\n",
       " ('Brazil', 10000.0),\n",
       " ('Canada', 43000.0),\n",
       " ('China', 9000.0),\n",
       " ('France', 39000.0),\n",
       " ('Germany', 44000.0),\n",
       " ('India', 2000.0),\n",
       " ('Indonesia', 4000.0),\n",
       " ('Italy', 32000.0),\n",
       " ('Japan', 39000.0),\n",
       " ('Mexico', 9000.0),\n",
       " ('Russia', 10000.0),\n",
       " ('South Korea', 22000.0),\n",
       " ('Spain', 28000.0),\n",
       " ('United Kingdom', 40000.0),\n",
       " ('United States', 59000.0)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, ROUND(gdp/(population*1000), 0) * 1000 AS pc_gdp\n",
    "FROM world\n",
    "WHERE gdp >= POWER(10, 12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Name and capital have the same length\n",
    "\n",
    "Greece has capital Athens.\n",
    "\n",
    "Each of the strings 'Greece', and 'Athens' has 6 characters.\n",
    "\n",
    "**Show the name and capital where the name and the capital have the same number of characters.**\n",
    "\n",
    "- You can use the [LENGTH](https://sqlzoo.net/wiki/LENGTH) function to find the number of characters in a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>capital</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Angola</td>\n",
       "        <td>Luanda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Yerevan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Botswana</td>\n",
       "        <td>Gaborone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Ottowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Djibouti</td>\n",
       "        <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egypt</td>\n",
       "        <td>Cairo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Estonia</td>\n",
       "        <td>Tallinn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fiji</td>\n",
       "        <td>Suva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gambia</td>\n",
       "        <td>Banjul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Georgia</td>\n",
       "        <td>Tbilisi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ghana</td>\n",
       "        <td>Accra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luxembourg</td>\n",
       "        <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mauritania</td>\n",
       "        <td>Nouakchott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>Asunción</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>Lima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Warsaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Moscow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rwanda</td>\n",
       "        <td>Kigali</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">26 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Algeria', 'Algiers'),\n",
       " ('Angola', 'Luanda'),\n",
       " ('Armenia', 'Yerevan'),\n",
       " ('Botswana', 'Gaborone'),\n",
       " ('Canada', 'Ottowa'),\n",
       " ('Djibouti', 'Djibouti'),\n",
       " ('Egypt', 'Cairo'),\n",
       " ('Estonia', 'Tallinn'),\n",
       " ('Fiji', 'Suva'),\n",
       " ('Gambia', 'Banjul'),\n",
       " ('Georgia', 'Tbilisi'),\n",
       " ('Ghana', 'Accra'),\n",
       " ('Greece', 'Athens'),\n",
       " ('Luxembourg', 'Luxembourg'),\n",
       " ('Mauritania', 'Nouakchott'),\n",
       " ('Paraguay', 'Asunción'),\n",
       " ('Peru', 'Lima'),\n",
       " ('Poland', 'Warsaw'),\n",
       " ('Russia', 'Moscow'),\n",
       " ('Rwanda', 'Kigali'),\n",
       " ('San Marino', 'San Marino'),\n",
       " ('Singapore', 'Singapore'),\n",
       " ('Taiwan', 'Taipei'),\n",
       " ('Togo', 'Lomé'),\n",
       " ('Turkey', 'Ankara'),\n",
       " ('Zambia', 'Lusaka')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, capital FROM world\n",
    "WHERE LENGTH(name) = LENGTH(capital)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Matching name and capital\n",
    "\n",
    "The capital of Sweden is Stockholm. Both words start with the letter 'S'.\n",
    "\n",
    "**Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.**\n",
    "\n",
    "- You can use the function [LEFT](https://sqlzoo.net/wiki/LEFT) to isolate the first character.\n",
    "- You can use <> as the **NOT EQUALS** operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>capital</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra</td>\n",
       "        <td>Andorra la Vella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbados</td>\n",
       "        <td>Bridgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belize</td>\n",
       "        <td>Belmopan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Brasília</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brunei</td>\n",
       "        <td>Bandar Seri Begawan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burundi</td>\n",
       "        <td>Bujumbura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "        <td>Guatemala City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>Georgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait</td>\n",
       "        <td>Kuwait City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maldives</td>\n",
       "        <td>Malé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marshall Islands</td>\n",
       "        <td>Majuro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>Mexico City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>Monaco-Ville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mozambique</td>\n",
       "        <td>Maputo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niger</td>\n",
       "        <td>Niamey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "        <td>Panama City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Papua New Guinea</td>\n",
       "        <td>Port Moresby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sao Tomé and Príncipe</td>\n",
       "        <td>São Tomé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>Seoul</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">24 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Algeria', 'Algiers'),\n",
       " ('Andorra', 'Andorra la Vella'),\n",
       " ('Barbados', 'Bridgetown'),\n",
       " ('Belize', 'Belmopan'),\n",
       " ('Brazil', 'Brasília'),\n",
       " ('Brunei', 'Bandar Seri Begawan'),\n",
       " ('Burundi', 'Bujumbura'),\n",
       " ('Guatemala', 'Guatemala City'),\n",
       " ('Guyana', 'Georgetown'),\n",
       " ('Kuwait', 'Kuwait City'),\n",
       " ('Maldives', 'Malé'),\n",
       " ('Marshall Islands', 'Majuro'),\n",
       " ('Mexico', 'Mexico City'),\n",
       " ('Monaco', 'Monaco-Ville'),\n",
       " ('Mozambique', 'Maputo'),\n",
       " ('Niger', 'Niamey'),\n",
       " ('Panama', 'Panama City'),\n",
       " ('Papua New Guinea', 'Port Moresby'),\n",
       " ('Sao Tomé and Príncipe', 'São Tomé'),\n",
       " ('South Korea', 'Seoul'),\n",
       " ('Sri Lanka', 'Sri Jayawardenepura Kotte'),\n",
       " ('Sweden', 'Stockholm'),\n",
       " ('Taiwan', 'Taipei'),\n",
       " ('Tunisia', 'Tunis')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, capital FROM world\n",
    "WHERE SUBSTRING(name, 1, 1) = SUBSTRING(capital, 1, 1) AND name <> capital"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. All the vowels\n",
    "\n",
    "**Equatorial Guinea** and **Dominican Republic** have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.\n",
    "\n",
    "**Find the country that has all the vowels and no spaces in its name.**\n",
    "\n",
    "- You can use the phrase name `NOT LIKE '%a%'` to exclude characters from your results.\n",
    "- The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mozambique</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mozambique',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world\n",
    "WHERE name LIKE '%a%' AND name LIKE '%e%' AND name LIKE '%i%' AND name LIKE '%o%' AND name LIKE '%u%'\n",
    "  AND name NOT LIKE '% %'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
